package com.pioneer.dao;

import com.pioneer.beans.Job;
import com.pioneer.beans.Supplier;
import com.pioneer.beans.Contractor;
import com.pioneer.beans.Item;
import com.pioneer.beans.JobItem;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;

import java.util.Properties;
import java.util.List;
import java.util.ArrayList;
import java.util.StringTokenizer;


/**
 * Title:        Pioneer Application
 * Description:
 * Copyright:    Copyright (c) 2001
 * Company:      Pioneer Metals
 * @author Gary Chen
 * @version 1.0
 */

public class JdbcJobDAO
	extends JdbcDAO
	implements JobDAO {

	
    public JdbcJobDAO(Properties properties)
	throws DataAccessException
	{
		super(properties);
    }
	
	/**
	* This is the jobs maintenance section
	*/

	public List getJobs()
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List jobs=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from job");
			while (rs.next())
			{
				jobs.add(new Job(rs.getString("quote_num"),
					rs.getDate("job_date"),rs.getString("job_name"),
					rs.getString("address"),rs.getString("city"),
					rs.getString("state"),rs.getString("zip"),
					rs.getString("sales_rep"),rs.getInt("qty"),rs.getString("type"),
					rs.getString("supplier_name"),rs.getString("contractor"),
					rs.getString("status"),	rs.getDate("ship_date"),
					rs.getString("terms")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return jobs;
    }
    public Job getJob(String quoteNum)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Job job=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from job where "+
			"quote_num='"+quoteNum+"'");
			if (rs.next())
			{
				job=new Job(rs.getString("quote_num"),
					rs.getDate("job_date"),rs.getString("job_name"),
					rs.getString("address"),rs.getString("city"),
					rs.getString("state"),rs.getString("zip"),
					rs.getString("sales_rep"),rs.getInt("qty"),rs.getString("type"),
					rs.getString("supplier_name"),rs.getString("contractor"),
					rs.getString("status"),	rs.getDate("ship_date"),
					rs.getString("terms")
					);				
			}
			if (job!=null) {
				List jobItems = getJobItems(job.getQuoteNum());
				job.setJobItems(jobItems);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return job;
    }
	
    public void addJob(Job job)
	throws DataAccessException {
		java.sql.Date jobDate=new java.sql.Date(job.getJobDate().getTime());
		java.sql.Date shipDate=new java.sql.Date(job.getShipDate().getTime());
		String sqlString= "INSERT INTO job (quote_num,job_date,job_name,address," +
			"city,state,zip,sales_rep,qty,type,supplier_name,contractor,status,"+
			"ship_date,terms) VALUES ('"+sqlSafe(job.getQuoteNum())+"','"+
		jobDate+"','"+sqlSafe(job.getJobName())+"','"+
		sqlSafe(job.getAddress())+"','"+sqlSafe(job.getCity())+"','"+
		sqlSafe(job.getState())+"','"+sqlSafe(job.getZip())+"','"+
		sqlSafe(job.getSalesRep())+"','"+job.getQty()+"','"+
		sqlSafe(job.getType())+"','"+sqlSafe(job.getSupplierName())+"','"+
		sqlSafe(job.getContractorName())+"','"+sqlSafe(job.getStatus())+"','"+
		shipDate+"','"+sqlSafe(job.getTerms())+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateJob(Job job)
	throws DataAccessException {
		java.sql.Date jobDate=new java.sql.Date(job.getJobDate().getTime());
		java.sql.Date shipDate=new java.sql.Date(job.getShipDate().getTime());
		
		String sqlString="UPDATE job SET job_date = '"+jobDate+
			"', job_name='"+sqlSafe(job.getJobName())+
			"', address='"+sqlSafe(job.getAddress())+
			"', city='"+sqlSafe(job.getCity())+
			"', state='"+sqlSafe(job.getState())+
			"', zip='"+sqlSafe(job.getZip())+
			"', sales_rep='"+sqlSafe(job.getSalesRep())+
			"', qty='"+job.getQty()+
			"', type='"+sqlSafe(job.getType())+
			"', supplier_name='"+sqlSafe(job.getSupplierName())+
			"', contractor='"+sqlSafe(job.getContractorName())+
			"', status='"+sqlSafe(job.getStatus())+
			"', ship_date='"+shipDate+
			"', terms='"+sqlSafe(job.getTerms())+
			"' WHERE quote_num='"+sqlSafe(job.getQuoteNum())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteJob(String quoteNum)
	throws DataAccessException {        
		updateStatement("DELETE FROM job WHERE quote_num='"+sqlSafe(quoteNum)+"'");
    }

	/**
	* This is the supplier maintenance section
	*/
	
	public List getSuppliers()
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List suppliers=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from supplier");
			while (rs.next())
			{
				suppliers.add(new Supplier(rs.getString("supplier_name"),
					rs.getString("contact"),
					rs.getString("address"),rs.getString("city"),
					rs.getString("state"),rs.getString("zip"),
					rs.getString("phone"),rs.getString("fax")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return suppliers;
    }
    public Supplier getSupplier(String supplierName)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Supplier supplier=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from supplier where "+
			"supplier_name='"+supplierName+"'");
			if (rs.next())
			{
				supplier=new Supplier(rs.getString("supplier_name"),
					rs.getString("contact"),
					rs.getString("address"),rs.getString("city"),
					rs.getString("state"),rs.getString("zip"),
					rs.getString("phone"),rs.getString("fax")
					);				
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return supplier;
    }
	
    public void addSupplier(Supplier supplier)
	throws DataAccessException {
		String sqlString= "INSERT INTO supplier (supplier_name,contact,address," +
			"city,state,zip,phone,fax) VALUES ('"+
			sqlSafe(supplier.getSupplierName())+"','"+
		sqlSafe(supplier.getContact())+"','"+
		sqlSafe(supplier.getAddress())+"','"+sqlSafe(supplier.getCity())+"','"+
		sqlSafe(supplier.getState())+"','"+sqlSafe(supplier.getZip())+"','"+
		sqlSafe(supplier.getPhone())+"','"+sqlSafe(supplier.getFax())+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateSupplier(Supplier supplier)
	throws DataAccessException {
		String sqlString="UPDATE supplier SET contact = '"+sqlSafe(supplier.getContact())+
			"', address='"+sqlSafe(supplier.getAddress())+
			"', city='"+sqlSafe(supplier.getCity())+
			"', state='"+sqlSafe(supplier.getState())+
			"', zip='"+sqlSafe(supplier.getZip())+
			"', phone='"+sqlSafe(supplier.getPhone())+
			"', fax='"+sqlSafe(supplier.getFax())+
			"' WHERE supplier_name='"+sqlSafe(supplier.getSupplierName())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteSupplier(String supplierName)
	throws DataAccessException {        
		updateStatement("DELETE FROM supplier WHERE supplier_name='"+sqlSafe(supplierName)+"'");
    }
	
	/**
	* This is the contractors maintenance section
	*/

	public List getContractors()
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List contractors=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from contractor");
			while (rs.next())
			{
				contractors.add(new Contractor(rs.getString("contractor_name"),
					rs.getString("contact"),
					rs.getString("phone"),rs.getString("fax")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return contractors;
    }
    public Contractor getContractor(String contractorName)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Contractor contractor=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from contractor where "+
			"contractor_name='"+contractorName+"'");
			if (rs.next())
			{
				contractor=new Contractor(rs.getString("contractor_name"),
					rs.getString("contact"),
					rs.getString("phone"),rs.getString("fax")
					);				
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return contractor;
    }
	
    public void addContractor(Contractor contractor)
	throws DataAccessException {
		String sqlString= "INSERT INTO contractor (contractor_name,contact,phone,fax) VALUES ('"+
			sqlSafe(contractor.getContractorName())+"','"+
		sqlSafe(contractor.getContact())+"','"+
		sqlSafe(contractor.getPhone())+"','"+sqlSafe(contractor.getFax())+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateContractor(Contractor contractor)
	throws DataAccessException {
		String sqlString="UPDATE contractor SET contact = '"+sqlSafe(contractor.getContact())+
			"', phone='"+sqlSafe(contractor.getPhone())+
			"', fax='"+sqlSafe(contractor.getFax())+
			"' WHERE contractor_name='"+sqlSafe(contractor.getContractorName())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteContractor(String contractorName)
	throws DataAccessException {        
		updateStatement("DELETE FROM contractor WHERE contractor_name='"+sqlSafe(contractorName)+"'");
    }
	
	/**
	* This is the items maintenance section
	*/

	public List getItems()
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List items=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from item");
			while (rs.next())
			{
				items.add(new Item(rs.getString("item_num"),
					rs.getString("description"),
					rs.getDouble("price"),rs.getDouble("cost")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return items;
    }
    public Item getItem(String itemName)
	throws DataAccessException {
		Statement stat=null;
		ResultSet rs=null;
		Item item=null;
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("select * from item where "+
			"item_name='"+itemName+"'");
			if (rs.next())
			{
				item=new Item(rs.getString("item_num"),
					rs.getString("description"),
					rs.getDouble("price"),rs.getDouble("cost")
					);				
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}
		return item;
    }
	
    public void addItem(Item item)
	throws DataAccessException {
		String sqlString= "INSERT INTO item (item_num,description,price,cost) VALUES ('"+
			sqlSafe(item.getItemNum())+"','"+
		sqlSafe(item.getDescription())+"','"+
		item.getPrice()+"','"+item.getCost()+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateItem(Item item)
	throws DataAccessException {
		String sqlString="UPDATE item SET description = '"+sqlSafe(item.getDescription())+
			"', price='"+item.getPrice()+
			"', cost='"+item.getCost()+
			"' WHERE item_num='"+sqlSafe(item.getItemNum())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteItem(String itemNum)
	throws DataAccessException {        
		updateStatement("DELETE FROM item WHERE item_num='"+sqlSafe(itemNum)+"'");
    }
	
	/**
	* This is the jobitems maintenance section
	*/

	public List getJobItems(String quoteNum)
	throws DataAccessException {

		Statement stat=null;
		ResultSet rs=null;
		List jobItems=new ArrayList();
		try
		{
			stat=connection.createStatement();
			rs=stat.executeQuery("SELECT * from job_items WHERE quote_num='"+quoteNum+"'");
			while (rs.next())
			{
				jobItems.add(new JobItem(rs.getString("quote_num"),
					rs.getString("item_num"),
					rs.getInt("qty")
					)	);
			}
		}
		catch (SQLException ex)
		{
			throw new DataAccessException(ex.getMessage());
		}
		finally
		{
			try
			{
				if (rs!=null) rs.close();
				if (stat!=null) stat.close();
			}
			catch (SQLException ex)
			{
				System.err.println(ex.getMessage());
			}
		}		
		return jobItems;
    }

    public void addJobItem(JobItem jobItem)
	throws DataAccessException {
		String sqlString= "INSERT INTO job_items "+
			"(quote_num,item_num,qty) VALUES ('"+
			sqlSafe(jobItem.getQuoteNum())+"','"+
		sqlSafe(jobItem.getItemNum())+"','"+
		jobItem.getQty()+"')";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

	public void updateJobItem(JobItem jobItem)
	throws DataAccessException {
		String sqlString="UPDATE job_items SET qty = "+jobItem.getQty()+
			" WHERE quote_num='"+sqlSafe(jobItem.getQuoteNum())+"' AND "+
			" item_num='"+sqlSafe(jobItem.getItemNum())+"'";
		System.out.println(sqlString);
		updateStatement(sqlString);
    }

    public void deleteJobItem(String quoteNum, String itemNum)
	throws DataAccessException {        
		updateStatement("DELETE FROM job_items WHERE quote_num='"+
			sqlSafe(quoteNum)+"' AND "+
			" item_num='"+sqlSafe(itemNum)+"'");
    }	
	
    public static void main(String[] args) {
		try
		{
		    Properties props=new Properties();
		    props.load(new java.io.FileInputStream("config"+java.io.File.separator+"db.prop"));
			JobDAO dao=new JdbcJobDAO(props);
			System.out.println("DAO loaded successfully");
			Supplier supplier = new Supplier("supplierName","contact","address",
				"city","CA","zip","213","fax");
			Contractor contractor  = new Contractor("contractorName","Gary","phone","fax");
			Job job = new Job("1","1/1/1999","jobName","address","city",
				"CA","zip","salesRep","0","type","supplierName",
				"contractorName","done","1/1/1999","terms");			
			Item item = new Item("123","new item",3.2,13);
			JobItem jobItem = new JobItem(job.getQuoteNum(),item.getItemNum(),4);
			//dao.addItem(item);
			dao.addSupplier(supplier);			
			dao.addContractor(contractor);			
			dao.addJob(job);
			dao.addJobItem(jobItem);
			
			//dao.getContractor("contractorName");
			//dao.getSupplier("supplierName");
			//dao.getJob("1");
			
			//dao.updateJob("1",job);
			//dao.updateContractor("contractorName",contractor);
			//dao.updateSupplier("supplierName",supplier);
			//dao.deleteJob("1");
			//dao.deleteSupplier("supplierName");
			//dao.deleteContractor("contractorName");
			
		}
		catch (Exception ex)
		{
			ex.printStackTrace();
		}
        //JdbcManifestDAO jdbcManifestDAO1 = new JdbcManifestDAO();
    }

}